package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.*;

public class SaleDB implements SaleDBIF {
	
	private  Connection con;
    /** Creates a new instance of DBProduct */
    public SaleDB() {
      con = DbConnection1.getInstance().getDBcon();
    }

	public ArrayList<Sale> getAllSales(boolean retrieveAssociation) {
		return miscWhere("", retrieveAssociation);
	}

	public Sale findSale(int saleID, boolean retrieveAssociation) {
		String wClause = "  saleID = '" + saleID + "'";
        return singleWhere(wClause, retrieveAssociation);
	}

	public int createSale(Sale s) throws Exception {
		//call to get the next ID number
        int nextID = GetMax.getMaxId("Select max(saleid) from sale");
        nextID = nextID + 1;
        System.out.println("next saleID = " +  nextID);
        String query =null;
        int rc = -1;
       if(s.getEvent()!=null)
       {   
       
	   query="INSERT INTO sale(saleid, saletotal, saledate, saleeventid, saleempid)  VALUES('"+
	  		nextID + "','" +
			s.getTotal()  + "','"  +
		    s.getDate() + "','"  +
		    s.getEvent().getEventId() + "','"  +
            s.getEmployee().getEmpId() + "')";
       }
       else{
    	   
    	   query="INSERT INTO sale(saleid, saletotal, saledate, saleeventid, saleempid)  VALUES('"+
    	  		nextID + "','" +
    			s.getTotal()  + "','"  +
    		    s.getDate() + "',"  +
                null + ", '" +
    		    s.getEmployee().getEmpId()+"')";
       }
       System.out.println("insert : " + query);
      try{ // insert new sale
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Sale ikke oprettet");
          throw new Exception ("Sale is not inserted correct");
       }
       return(rc);

	}

	@Override
	public int updateSale(Sale s) {
		// TODO Auto-generated method stub
		return 0;
	}

	public int deleteSale(int saleID) {
		int rc=-1;
		  
	  	String query="DELETE FROM sale WHERE saleID = '" +
	  			saleID + "'";
                System.out.println(query);
	  	try{ // delete from sale
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in sale db: "+ex);
   	        }
		return(rc);
	}
	
	private Sale singleWhere(String wClause, boolean retrieveAssociation)
    {	
        ResultSet results;
        Sale sObj = new Sale();
        String query =  buildQuery(wClause);
        System.out.println("DbSale -singleWhere " + query); 
        try{ // read from Sale
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);

	   if( results.next() ){
	       sObj = buildSale(results);
               //missing the test on retriveassociation
               
           }//end if
	   stmt.close();
	   if(retrieveAssociation)
       {   //The Event is to be build as well
		   int eventId = sObj.getEvent().getEventId();
           EventDB dbEvent= new EventDB();
           Event e = dbEvent.findEvent(eventId, false);
           sObj.setEvent(e);
           System.out.println("Event is seleceted");
           int empId = sObj.getEmployee().getEmpId();
           EmployeeDB dbEmployee= new EmployeeDB();
           Employee emp = dbEmployee.findEmployee(empId, false);
           sObj.setEmployee(emp);
           System.out.println("Employee is seleceted");
          
	}//end try	
        else{ //no sale was found
            sObj = null;
        }
        }
	catch(Exception e){
	   System.out.println("Query exception - select Event : "+e);
	   e.printStackTrace();
        }
	return sObj;
    }
	
	 //miscWhere is used when more than one Product is selected and build
    private ArrayList<Sale> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Sale> list = new ArrayList<Sale>();
		
		String query =  buildQuery(wClause);
                System.out.println("DbSale " + query); 
  		try{ // read from Sale
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 	
			while( results.next() ){
				Sale sObj = new Sale();
			 sObj = buildSale(results);	
                         list.add(sObj);
                         //missing test on retriveAssociation
			}//end while
			stmt.close();
			if(retrieveAssociation)
            {   //The supervisor and department is to be build as well
                for(Sale sObj : list){
         		   int eventId = sObj.getEvent().getEventId();
                   EventDB dbEvent= new EventDB();
                   Event e = dbEvent.findEvent(eventId, false);
                   sObj.setEvent(e);
                   System.out.println("Event is seleceted");
                   int empId = sObj.getEmployee().getEmpId();
                   EmployeeDB dbEmployee= new EmployeeDB();
                   Employee emp = dbEmployee.findEmployee(empId, false);
                   sObj.setEmployee(emp);
                   System.out.println("Employee is seleceted");
                }
            }//end if   
		
			
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select sale : "+e);
			e.printStackTrace();
	 	}
		return list;
	}
    
    private Sale buildSale(ResultSet results)
    {
    	Sale sObj = new Sale();
    	Event eObj = new Event();
    	Employee empObj = new Employee();
       
         try{
               sObj.setSaleId(results.getInt("saleid"));
               sObj.setTotal(results.getDouble("saletotal"));
               sObj.setDate(results.getString("saledate"));
               eObj.setEventId(results.getInt("saleEventId"));
               sObj.setEvent(eObj);
               empObj.setEmpId(results.getInt("saleEmpId"));
               sObj.setEmployee(empObj);
          }
         catch(Exception e)
         {
             System.out.println("building Sale object");
         }
        
        return sObj;
        
    }
    
    private String buildQuery(String wClause)
	{
            String query="SELECT saleID, saletotal, saledate, saleeventid, saleempid FROM sale, event, employee where saleeventid=eventid and saleempid =empid"; 
            
           

		if (wClause.length()>0)
			query = query + "  and " + wClause;
			
		return query;
	}

}
